﻿using FlashPay.DAO.Interface;
using FlashPay.DAO.Interface.Receipt;
using FlashPay.DAO.Shared;
using FlashPay.EF;
using FlashPay.EF.Models;
using FlashPay.Entity;
using FlashPay.Entity.Enum;
using FlashPay.Entity.Parameter;
using FlashPay.Entity.Request.Receipt;
using FlashPay.Entity.Response.Payment;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FlashPay.DAO.Impl.Receive
{
    public class ReceiptDaoImpl : BaseDAO, ReceiptDao
    {
        #region 注入
        /// <summary>
        /// EF上下文
        /// </summary>
        //private FlashPayDynamicContext _context { set; get; }
        //public ReceiptDaoImpl()
        //{
        //    _context = new FlashPayDynamicContext(DateTime.Now.ToString("MM"));
        //}

        private FlashPayContext _context { set; get; }
        public ReceiptDaoImpl(FlashPayContext context)
        {
            _context = context;
        }

        public void Dispose()
        {
            if (_context != null)
            {
                _context.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 获取订单月份
        /// </summary>
        /// <param name="orderNo">订单号</param>
        /// <returns></returns>
        private string GetOrderMonth(long orderNo)
        {
            return orderNo.ToString().Substring(4, 2);
        }

        /// <summary>
        /// 根据编号获取存款记录
        /// </summary>
        /// <param name="orderNo">编号</param>
        /// <returns>Company</returns>
        public DepositRecord Get(long orderNo)
        {
            var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"DepositRecord_{month}";
            }

            var sql = $"SELECT * FROM {tableName} WHERE OrderNo={orderNo}";

            var depositRecord = _context.DepositRecord
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return depositRecord;
        }

        /// <summary>
        /// 收款卡查询
        /// </summary>
        /// <param name="query">查询参数</param>
        /// <returns></returns>
        public DataGrid<PaymentCardResponse> GetReceiptCard(PaymentCardQuery query)
        {
            var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var sql = $"select * from {tableName} where NoticeStatus<>{(sbyte)DepositRecordNoticeStatus.不通知} and DepositDate >= '{DateTime.Now.ToString("yyyy-MM-dd 00:00:00")}' AND DepositDate<='{DateTime.Now.ToString("yyyy-MM-dd 23:59:59")}'";
            var depositRecords = _context.DepositRecord
                .FromSql(sql)
                .AsNoTracking()
                .ToList();

            var q = from bc in _context.BankCard
                    let quantity = (from dr in depositRecords where dr.DepositCardId == bc.Bcid select dr).Count()
                    let depositAmountSum = (from dr in depositRecords where dr.DepositCardId == bc.Bcid select dr).Sum(p => p.DepositAmount)
                    let transportCard = (from tc in _context.TransportCard where tc.Bcid == bc.Bcid select tc).FirstOrDefault()
                    join
                    c in _context.Company on bc.CompanyId equals c.CompanyId
                    join
                    bcel in _context.BankCardExtraLimit on bc.CardNumber equals bcel.CardNumber
                    //join
                    //tc in _context.TransportCard on bc.Bcid equals tc.Bcid
                    //只有已分配公司，且是收款卡
                    where new List<sbyte>() { (sbyte)CardType.收款卡,(sbyte)CardType.中转卡 }.Contains(bc.CardType) && query.CompanyIds.Contains(bc.CompanyId)

                    select new
                    {
                        c.CompanyName,
                        c.CompanyNameEn,
                        bc.Bcid,
                        bc.BankName,
                        bc.CardNumber,
                        bc.CardName,
                        bc.CardType,
                        bc.UsingStatus,
                        bc.EnableStatus,
                        bc.PaymentStart,
                        bc.PaymentEnd,
                        bc.PayFeeRatio,
                        bc.DepositType,
                        bc.CrossBankPay,
                        bc.DepositFeeRatio,
                        //,AdjustBalanceResponses =x
                        bc.Balance,
                        bc.BalanceLastUpdate,
                        bc.IpAddress,
                        bc.Remark,
                        bc.VmClientId,
                        bc.CreateDate,
                        Quantity = quantity,
                        DepositAmount = depositAmountSum,
                        bcel.LimitDepositAmount,
                        bcel.LimitDepositCount,
                        ShowedOnDepositCard=(transportCard==null? (bc.CardType == (sbyte)CardType.收款卡?1:2) : (bc.CardType == (sbyte)CardType.收款卡 ? 1 : transportCard.ShowedOnDepositCard))
                    };

            #region 搜索条件
            q = q.Where(c => c.ShowedOnDepositCard == 1);

            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                q = q.Where(c => c.CompanyName.Contains(query.CompanyName));
            }
            //銀行名称
            if (!string.IsNullOrEmpty(query.BankName))
            {
                q = q.Where(c => c.BankName.Contains(query.BankName));
            }
            //銀行卡號
            if (!string.IsNullOrEmpty(query.CardNumber))
            {
                q = q.Where(c => c.CardNumber.Contains(query.CardNumber));
            }
            //銀行卡用戶名
            if (!string.IsNullOrEmpty(query.CardName))
            {
                q = q.Where(c => c.CardName.Contains(query.CardName));
            }
            //启用状态
            if (query.EnableStatus == 1 || query.EnableStatus == 2)
            {
                q = q.Where(c => c.EnableStatus == query.EnableStatus);
            }
            //更新时间排序
            if (query.LastUpdateDateSortType.HasValue)
            {
                if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.正常.GetHashCode())
                {
                    q = q.OrderByDescending(c => c.BalanceLastUpdate);
                }
                else if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.不正常.GetHashCode())
                {
                    q = q.OrderBy(c => c.BalanceLastUpdate);
                }
            }
            #endregion

            #region 模型转换
            var depositAmount = new decimal(0);

            var paymentCardResponses = new List<PaymentCardResponse>();

            var list = q.Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();
            var totalCount = q.Count();
            list.ForEach(item =>
            {
                depositAmount += item.DepositAmount;

                var paymentCardResponse = new PaymentCardResponse()
                {
                    CompanyName = item.CompanyName,
                    CompanyNameEn = item.CompanyNameEn,
                    BcId = item.Bcid,
                    BankName = item.BankName,
                    CardNumber = Util.WebUtil.SetCardNumber(item.CardNumber),
                    CardName = item.CardName,
                    CardType = item.CardType,
                    UsingStatus = item.UsingStatus,
                    EnableStatus = item.EnableStatus,
                    PayFeeRatio = item.PayFeeRatio,
                    DepositType = item.DepositType,
                    CrossBankPay = item.CrossBankPay,
                    DepositFeeRatio = item.DepositFeeRatio,
                    Balance = item.Balance.HasValue ? item.Balance.Value.ToString("#0.00") : null,
                    BalanceLastUpdate = item.BalanceLastUpdate.HasValue ? item.BalanceLastUpdate.Value.ToString("yyyy-MM-dd HH:mm:ss") : null,
                    IpAddress = item.IpAddress,
                    Remark = "*.*.*.*",
                    VmClientId = item.VmClientId,
                    Quantity= item.Quantity,
                    DepositAmount = item.DepositAmount.ToString("#0.00"),
                    LimitDepositAmount = item.LimitDepositAmount,
                    LimitDepositCount = item.LimitDepositCount,
                    Limit = false
                };
                //银行卡号权限
                if (query.UserPermission.Contains("ReceiptCard0005"))
                {
                    paymentCardResponse.CardNumber = item.CardNumber;
                }

                if (item.LimitDepositCount.HasValue)
                {
                    if (item.Quantity > item.LimitDepositCount.Value && item.LimitDepositCount.Value>0)
                    {
                        paymentCardResponse.Limit = true;
                    }
                }

                if (item.LimitDepositAmount.HasValue)
                {
                    if (item.DepositAmount > item.LimitDepositAmount.Value && item.LimitDepositAmount.Value>0)
                    {
                        paymentCardResponse.Limit = true;
                    }
                }
                if (query.UserPermission.Contains("ReceiptCard0002"))
                {
                    paymentCardResponse.Remark = item.Remark;
                }

                if (item.BalanceLastUpdate.HasValue)
                {
                    var balanceLastUpdate = item.BalanceLastUpdate.Value;
                    var ts = DateTime.Now.Subtract(balanceLastUpdate).TotalMinutes;
                    if (ts > 0)
                    {
                        paymentCardResponse.TimeDifference = ts;
                    }
                }
                paymentCardResponses.Add(paymentCardResponse);
            });



            #endregion

            return new DataGrid<PaymentCardResponse>()
            {
                Total = totalCount,
                Rows = paymentCardResponses,
                Success = true
            };
        }

        // 获取付款记录列表
        public PagedList<DepositOrPaymentRecordResponse> GetReceiptRecordList(PaymentRecordQuery query)
        {
            PagedList<DepositOrPaymentRecordResponse> result = new PagedList<DepositOrPaymentRecordResponse>()
            {
                Success = false
            };
            try
            {
                if (query.StartTime == null || query.EndTime == null || query.CompanyIds == null)
                {
                    result.ErrorMessage = "参数错误";
                    return result;
                }

                var sql = string.Format("call {0}('{1}', '{2}', '{3}');", "sp_SelectDepositRecord", query.StartTime, query.EndTime, string.Join(", ", query.CompanyIds));

                var q = _context.DepositRecord.FromSql(sql).OrderByDescending(p=>p.CreateDbdate).ToList();

                //订单号
                if (!string.IsNullOrEmpty(query.OrderNo))
                {
                    q = q.Where(c => c.OrderNo.ToString().Contains(query.OrderNo)).ToList();
                }

                //客户姓名
                if (!string.IsNullOrEmpty(query.WithdrawalAccountName))
                {
                    q = q.Where(c => c.ClientAccountName.Contains(query.WithdrawalAccountName)).ToList();
                }

                //通知状态
                if (query.NoticeStatus > -1)
                {
                    q = q.Where(c => c.NoticeStatus == query.NoticeStatus).ToList();
                }

                //搜索收款卡号
                if (query.DepositCardID!=0) {
                    q = q.Where(c => c.DepositCardId == query.DepositCardID).ToList();
                }

                //存款类型
                if (query.TransTypeList != null && query.TransTypeList.Any())
                {
                    q = q.Where(c => query.TransTypeList.Contains(c.Transtype)).ToList();
                }

                var companys = _context.Company.ToList();
                var bankCards = _context.BankCard.ToList();

                //收款名称
                if (!string.IsNullOrEmpty(query.CardName))
                {
                    var cardSearch = bankCards.Where(p => p.CardName.Contains(query.CardName)).ToList();
                    if (cardSearch != null && cardSearch.Any())
                    {
                        q = q.Where(c => cardSearch.Select(p => p.Bcid).Contains(c.DepositCardId)).ToList();
                    }
                    else
                    {
                        q = q.Where(c => c.DepositCardId == -1).ToList();
                    }
                }

                #region 新增搜索条件
                //匹配流水号
                if (query.Type == 1)
                {
                    q = q.Where(c => c.DepositMatchID == null).ToList();
                }

                //收款金额
                if (query.DepositAmount > 0)
                {
                    q = q.Where(c => c.DepositAmount == query.DepositAmount).ToList();
                }

                //银行卡编号
                if (!string.IsNullOrEmpty(query.BankCode))
                {
                    var cardSearch = bankCards.Where(p => p.BankCode.Contains(query.BankCode)).ToList();
                    if (cardSearch != null && cardSearch.Any())
                    {
                        q = q.Where(c => cardSearch.Select(p => p.Bcid).Contains(c.DepositCardId)).ToList();
                    }
                    else
                    {
                        q = q.Where(c => c.DepositCardId == -1).ToList();
                    }
                }
                #endregion

                var list = q.OrderByDescending(e => e.CreateDbdate).Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();

                #region 模型转换
                var depositOrPaymentRecordResponses = new List<DepositOrPaymentRecordResponse>();

                list.ForEach(item =>
                {
                    var depositOrPaymentRecordResponse = new DepositOrPaymentRecordResponse();

                    #region 公司
                    var companyName = "";
                    var companyNameEn = "";
                    if (companys != null && companys.Any())
                    {
                        var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                        if (company != null)
                        {
                            companyName = company.CompanyName;
                            companyNameEn = company.CompanyNameEn;
                        }
                    }
                    #endregion

                    #region 银行卡
                    var bankName = "";
                    var cardName = "";
                    var cardNumber = "";
                    if (bankCards != null && bankCards.Any())
                    {
                        var bankCard = bankCards.FirstOrDefault(p => p.Bcid == item.DepositCardId);
                        if (bankCard != null)
                        {
                            bankName = bankCard.BankName;
                            cardName = bankCard.CardName;
                            cardNumber = bankCard.CardNumber;
                        }
                    }
                    #endregion

                    depositOrPaymentRecordResponse.OrderNo = item.OrderNo.ToString();
                    depositOrPaymentRecordResponse.CompanyId = item.CompanyId;
                    depositOrPaymentRecordResponse.CompanyName = companyName;
                    depositOrPaymentRecordResponse.CompanyNameEn = companyNameEn;
                    depositOrPaymentRecordResponse.BankName = bankName;
                    depositOrPaymentRecordResponse.CardName = cardName;
                    depositOrPaymentRecordResponse.CardNumber = Util.WebUtil.SetCardNumber(cardNumber);

                    depositOrPaymentRecordResponse.WithdrawalOrderNo = item.DepositNo;
                    depositOrPaymentRecordResponse.WithdrawalAmount = item.DepositAmount.ToString("#0.00");//收款余额
                    depositOrPaymentRecordResponse.PaymentDate = item.DepositDate;       //收款时间
                    //存款等级这个字段没啥用，用来显示交易类型
                    depositOrPaymentRecordResponse.DepositType = GetTransType(item.Transtype);
                    depositOrPaymentRecordResponse.WithdrawalAccountName = item.ClientAccountName;
                    depositOrPaymentRecordResponse.WithdrawalBankName = item.ClientBankName;
                    depositOrPaymentRecordResponse.WithdrawalCardNumber = item.ClientCardNumber;

                    depositOrPaymentRecordResponse.PostScript = item.PostScript;
                    depositOrPaymentRecordResponse.NoticeStatus = item.NoticeStatus;
                    if (item.NoticeLastTime != null)
                    {
                        depositOrPaymentRecordResponse.NoticeLastDate = item.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    depositOrPaymentRecordResponse.NoticeTimes = item.NoticeTimes;
                    depositOrPaymentRecordResponse.CreateDbdate = item.CreateDbdate;
                    depositOrPaymentRecordResponse.DepositRemark = string.IsNullOrEmpty(item.DepositRemark) ? "" : item.DepositRemark;
                    //(分钟)(当前时间-收款记录创建时间)>20
                    depositOrPaymentRecordResponse.TimeDifference = DateTime.Now.Subtract(item.CreateDate).TotalMinutes;
                    depositOrPaymentRecordResponses.Add(depositOrPaymentRecordResponse);
                });
                #endregion

                result.TotalCount = q.Count();
                result.TData = depositOrPaymentRecordResponses;
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.ErrorMessage = "服务器异常";
            }
            return result;
        }

        /// <summary>
        /// 存款记录查询
        /// </summary>
        /// <param name="query">查询条件</param>
        public DataGrid<DepositRecordResponse> GetReceiptRecord(DepositRecordQuery query)
        {
            var result = new DataGrid<DepositRecordResponse>()
            {
                Success = false
            };

            try
            {
                if (query.StartTime == null || query.EndTime == null || query.CompanyIds == null)
                {
                    throw new Exception("参数错误");
                }

                var companys = _context.Company.ToList();
                var bankCards = _context.BankCard.ToList();

                var sql = string.Format("call {0}('{1}', '{2}', '{3}');", "sp_SelectDepositRecord", query.StartTime, query.EndTime, string.Join(", ", query.CompanyIds));

                var q = _context.DepositRecord.FromSql(sql);

                //公司名称
                if (!string.IsNullOrEmpty(query.CompanyName))
                {
                    var companySearch = companys.Where(p => p.CompanyName.Contains(query.CompanyName)).ToList();
                    q = q.Where(c => companySearch.Select(p => p.CompanyId).Contains(c.CompanyId));
                }
                //订单号
                if (query.OrderNo.HasValue)
                {
                    q = q.Where(c => c.OrderNo == query.OrderNo.Value);
                }

                //客户姓名
                if (!string.IsNullOrEmpty(query.WithdrawalAccountName))
                {
                    q = q.Where(c => c.ClientAccountName.Contains(query.WithdrawalAccountName));
                }

                //通知状态
                if (query.NoticeStatus.HasValue)
                {
                    if (query.NoticeStatus == -1)
                    {
                        q = q.Where(c => c.NoticeStatus != (sbyte)DepositRecordNoticeStatus.不通知);
                    }
                    else {
                        q = q.Where(c => c.NoticeStatus == query.NoticeStatus.Value);
                    }
                }
                //收款名称
                if (!string.IsNullOrEmpty(query.CardName))
                {
                    var cardSearch = bankCards.Where(p => p.CardName.Contains(query.CardName)).ToList();
                    if (cardSearch != null && cardSearch.Any())
                    {
                        q = q.Where(c => cardSearch.Select(p => p.Bcid).Contains(c.DepositCardId));
                    }
                    else
                    {
                        q = q.Where(c => c.DepositCardId == -1);
                    }
                }
                //付款银行名称
                if (!string.IsNullOrEmpty(query.BankName))
                {
                    var bankCardIds = bankCards.Where(p => p.BankName.Contains(query.BankName)).Select(p => p.Bcid).ToList();
                    if (bankCardIds != null && bankCardIds.Any())
                    {
                        q = q.Where(c => bankCardIds.Contains(c.DepositCardId));
                    }
                    else
                    {
                        q = q.Where(c => bankCardIds.Contains(0));
                    }
                }
                //存款类型
                if (query.TransTypeList != null && query.TransTypeList.Any())
                {
                    q = q.Where(c => query.TransTypeList.Contains(c.Transtype));
                }

                #region 新增搜索条件
                //匹配流水号
                if (query.Type == 1)
                {
                    q = q.Where(c => c.DepositMatchID == null);
                }

                //收款金额
                if (query.DepositAmount > 0)
                {
                    q = q.Where(c => c.DepositAmount == query.DepositAmount);
                }
                //收款卡号
                if (query.DepositCardID != 0)
                {
                    q = q.Where(c => c.DepositCardId == query.DepositCardID);
                }
                //银行卡编号
                if (!string.IsNullOrEmpty(query.BankCode))
                {
                    var cardSearch = bankCards.Where(p => p.BankCode.Contains(query.BankCode)).ToList();
                    if (cardSearch != null && cardSearch.Any())
                    {
                        q = q.Where(c => cardSearch.Select(p => p.Bcid).Contains(c.DepositCardId));
                    }
                    else
                    {
                        q = q.Where(c => c.DepositCardId == -1);
                    }
                }
                #endregion

                var list = q.OrderByDescending(e => e.CreateDbdate).Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();

                #region 模型转换
                var withdrawalAmount = new decimal(0);

                var depositOrPaymentRecordResponses = new List<DepositRecordResponse>();

                list.ForEach(item =>
                {
                    var depositOrPaymentRecordResponse = new DepositRecordResponse();

                    #region 公司
                    var companyName = "";
                    var companyNameEn = "";
                    if (companys != null && companys.Any())
                    {
                        var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                        if (company != null)
                        {
                            companyName = company.CompanyName;
                            companyNameEn = company.CompanyNameEn;
                        }
                    }
                    #endregion

                    #region 银行卡
                    var bankName = "";
                    var cardName = "";
                    var cardNumber = "";
                    if (bankCards != null && bankCards.Any())
                    {
                        var bankCard = bankCards.FirstOrDefault(p => p.Bcid == item.DepositCardId);
                        if (bankCard != null)
                        {
                            bankName = bankCard.BankName;
                            cardName = bankCard.CardName;
                            cardNumber = bankCard.CardNumber;
                        }
                    }
                    #endregion

                    withdrawalAmount += item.DepositAmount;

                    depositOrPaymentRecordResponse.OrderNo = item.OrderNo.ToString();
                    depositOrPaymentRecordResponse.CompanyId = item.CompanyId;
                    depositOrPaymentRecordResponse.CompanyName = companyName;
                    depositOrPaymentRecordResponse.CompanyNameEn = companyNameEn;
                    depositOrPaymentRecordResponse.BankName = bankName;
                    depositOrPaymentRecordResponse.CardName = cardName;
                    depositOrPaymentRecordResponse.CardNumber = Util.WebUtil.SetCardNumber(cardNumber);

                    depositOrPaymentRecordResponse.WithdrawalOrderNo = item.DepositNo;
                    depositOrPaymentRecordResponse.WithdrawalAmount = item.DepositAmount.ToString("#0.00");//收款余额
                    depositOrPaymentRecordResponse.DepositDate = item.DepositDate;       //收款时间
                    //存款等级这个字段没啥用，用来显示交易类型
                    depositOrPaymentRecordResponse.DepositType = GetTransType(item.Transtype);
                    depositOrPaymentRecordResponse.WithdrawalAccountName = item.ClientAccountName;
                    depositOrPaymentRecordResponse.WithdrawalBankName = item.ClientBankName;
                    depositOrPaymentRecordResponse.WithdrawalCardNumber = item.ClientCardNumber;

                    depositOrPaymentRecordResponse.PostScript = item.PostScript;
                    depositOrPaymentRecordResponse.NoticeStatus = item.NoticeStatus;
                    if (item.NoticeLastTime != null)
                    {
                        depositOrPaymentRecordResponse.NoticeLastDate = item.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    depositOrPaymentRecordResponse.NoticeTimes = item.NoticeTimes;
                    depositOrPaymentRecordResponse.CreateDbdate = item.CreateDbdate;
                    depositOrPaymentRecordResponse.DepositRemark = string.IsNullOrEmpty(item.DepositRemark) ? "" : item.DepositRemark;
                    //(分钟)(当前时间-收款记录创建时间)>20
                    var ts = DateTime.Now.Subtract(item.CreateDate).TotalMinutes;
                    if (ts > 0)
                    {
                        depositOrPaymentRecordResponse.TimeDifference = ts;
                    }

                    if (query.UserPermission.Contains("ReceiptRecord0003")) {
                        depositOrPaymentRecordResponse.MakeUpPermission = "ReceiptRecord0003";
                    }

                    if (query.UserPermission.Contains("ReceiptRecord0005"))
                    {
                        depositOrPaymentRecordResponse.ChangeCompanyPermission = "ReceiptRecord0005";
                    }
                    depositOrPaymentRecordResponse.DepositMatchId = item.DepositMatchID.HasValue ? item.DepositMatchID.Value.ToString() : "";
                    depositOrPaymentRecordResponses.Add(depositOrPaymentRecordResponse);
                });

                var footers = new List<DepositRecordResponse>()
                {
                    new DepositRecordResponse(){
                        CardNumber = "订单总额",
                        WithdrawalAmount = withdrawalAmount.ToString("#0.00")
                    }
                };
                #endregion

                result.Total = q.Count();
                result.Rows = depositOrPaymentRecordResponses;
                result.Footer = footers;
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.ErrorMessage = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 获取匹配存款记录
        /// </summary>
        /// <param name="query">查询条件</param>
        public DataGrid<DepositRecordResponse> GetMatchDepositRecord(DepositRecordQuery query)
        {
            var result = new DataGrid<DepositRecordResponse>()
            {
                Success = false
            };

            try
            {
                if (query.StartTime == null || query.EndTime == null || query.CompanyIds == null)
                {
                    throw new Exception("参数错误");
                }

                var companys = _context.Company.ToList();
                var bankCards = _context.BankCard.ToList();

                var sql = string.Format("call {0}('{1}', '{2}', '{3}');", "sp_SelectDepositRecord", query.StartTime, query.EndTime, string.Join(", ", query.CompanyIds));

                var q = _context.DepositRecord.FromSql(sql);

                //公司名称
                if (!string.IsNullOrEmpty(query.CompanyName))
                {
                    var companySearch = companys.Where(p => p.CompanyName.Contains(query.CompanyName)).ToList();
                    q = q.Where(c => companySearch.Select(p => p.CompanyId).Contains(c.CompanyId));
                }
                //订单号
                if (query.OrderNo.HasValue)
                {
                    q = q.Where(c => c.OrderNo == query.OrderNo.Value);
                }

                //客户姓名
                if (!string.IsNullOrEmpty(query.WithdrawalAccountName))
                {
                    q = q.Where(c => c.ClientAccountName.Contains(query.WithdrawalAccountName));
                }

                //通知状态
                if (query.NoticeStatus.HasValue)
                {
                    if (query.NoticeStatus == -1)
                    {
                        q = q.Where(c => c.NoticeStatus != (sbyte)DepositRecordNoticeStatus.不通知);
                    }
                    else
                    {
                        q = q.Where(c => c.NoticeStatus == query.NoticeStatus.Value);
                    }
                }
                //收款名称
                if (!string.IsNullOrEmpty(query.CardName))
                {
                    var cardSearch = bankCards.Where(p => p.CardName.Contains(query.CardName)).ToList();
                    if (cardSearch != null && cardSearch.Any())
                    {
                        q = q.Where(c => cardSearch.Select(p => p.Bcid).Contains(c.DepositCardId));
                    }
                    else
                    {
                        q = q.Where(c => c.DepositCardId == -1);
                    }
                }
                //付款银行名称
                if (!string.IsNullOrEmpty(query.BankName))
                {
                    var bankCardIds = bankCards.Where(p => p.BankName.Contains(query.BankName)).Select(p => p.Bcid).ToList();
                    if (bankCardIds != null && bankCardIds.Any())
                    {
                        q = q.Where(c => bankCardIds.Contains(c.DepositCardId));
                    }
                    else
                    {
                        q = q.Where(c => bankCardIds.Contains(0));
                    }
                }
                //存款类型
                if (query.TransTypeList != null && query.TransTypeList.Any())
                {
                    q = q.Where(c => query.TransTypeList.Contains(c.Transtype));
                }

                //收款匹配编号
                if (query.DepositMatchID.HasValue)
                {
                    q = q.Where(c => c.DepositMatchID == null);
                }

                //收款金额
                if (query.DepositAmount > 0)
                {
                    q = q.Where(c => c.DepositAmount == query.DepositAmount);
                }
                //收款卡号
                if (query.DepositCardID != 0)
                {
                    q = q.Where(c => c.DepositCardId == query.DepositCardID);
                }
                //银行卡编号
                if (!string.IsNullOrEmpty(query.BankCode))
                {
                    var cardSearch = bankCards.Where(p => p.BankCode.Contains(query.BankCode)).ToList();
                    if (cardSearch != null && cardSearch.Any())
                    {
                        q = q.Where(c => cardSearch.Select(p => p.Bcid).Contains(c.DepositCardId));
                    }
                    else
                    {
                        q = q.Where(c => c.DepositCardId == -1);
                    }
                }
                //附言
                if (!string.IsNullOrEmpty(query.PostScript))
                {
                    q = q.Where(c => c.PostScript == query.PostScript);
                }
                //客户卡号
                if (!string.IsNullOrEmpty(query.WithdrawalCardNumber)) {

                    var before4String = query.WithdrawalCardNumber.Substring(0, 4);
                    var last4String = query.WithdrawalCardNumber.Substring(query.WithdrawalCardNumber.Length - 4, 4);

                    //q = q.Where(c => c.ClientCardNumber.Substring(0,4).Equals(before4String) && c.ClientCardNumber.Substring(c.ClientCardNumber.Length-4,4).Equals(last4String));

                    q = q.Where(c => c.ClientCardNumber.Substring(c.ClientCardNumber.Length - 4, 4).Equals(last4String));

                }
                var list = q.OrderByDescending(e => e.CreateDbdate).ToList();

                #region 模型转换
                var withdrawalAmount = new decimal(0);

                var depositOrPaymentRecordResponses = new List<DepositRecordResponse>();

                list.ForEach(item =>
                {
                    var depositOrPaymentRecordResponse = new DepositRecordResponse();

                    #region 公司
                    var companyName = "";
                    var companyNameEn = "";
                    if (companys != null && companys.Any())
                    {
                        var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                        if (company != null)
                        {
                            companyName = company.CompanyName;
                            companyNameEn = company.CompanyNameEn;
                        }
                    }
                    #endregion

                    #region 银行卡
                    var bankName = "";
                    var cardName = "";
                    var cardNumber = "";
                    if (bankCards != null && bankCards.Any())
                    {
                        var bankCard = bankCards.FirstOrDefault(p => p.Bcid == item.DepositCardId);
                        if (bankCard != null)
                        {
                            bankName = bankCard.BankName;
                            cardName = bankCard.CardName;
                            cardNumber = bankCard.CardNumber;
                        }
                    }
                    #endregion

                    withdrawalAmount += item.DepositAmount;

                    depositOrPaymentRecordResponse.DepositMatchId = query.DepositMatchID.Value.ToString();
                    depositOrPaymentRecordResponse.OrderNo = item.OrderNo.ToString();
                    depositOrPaymentRecordResponse.CompanyName = companyName;
                    depositOrPaymentRecordResponse.CompanyNameEn = companyNameEn;
                    depositOrPaymentRecordResponse.BankName = bankName;
                    depositOrPaymentRecordResponse.CardName = cardName;
                    depositOrPaymentRecordResponse.CardNumber = Util.WebUtil.SetCardNumber(cardNumber);

                    depositOrPaymentRecordResponse.WithdrawalOrderNo = item.DepositNo;
                    depositOrPaymentRecordResponse.WithdrawalAmount = item.DepositAmount.ToString("#0.00");//收款余额
                    depositOrPaymentRecordResponse.DepositDate = item.DepositDate;       //收款时间
                    //存款等级这个字段没啥用，用来显示交易类型
                    depositOrPaymentRecordResponse.DepositType = GetTransType(item.Transtype);
                    depositOrPaymentRecordResponse.WithdrawalAccountName = item.ClientAccountName;
                    depositOrPaymentRecordResponse.WithdrawalBankName = item.ClientBankName;
                    depositOrPaymentRecordResponse.WithdrawalCardNumber = item.ClientCardNumber;

                    depositOrPaymentRecordResponse.PostScript = item.PostScript;
                    depositOrPaymentRecordResponse.NoticeStatus = item.NoticeStatus;
                    if (item.NoticeLastTime != null)
                    {
                        depositOrPaymentRecordResponse.NoticeLastDate = item.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    depositOrPaymentRecordResponse.NoticeTimes = item.NoticeTimes;
                    depositOrPaymentRecordResponse.CreateDbdate = item.CreateDbdate;
                    depositOrPaymentRecordResponse.DepositRemark = string.IsNullOrEmpty(item.DepositRemark) ? "" : item.DepositRemark;
                    //(分钟)(当前时间-收款记录创建时间)>20
                    var ts = DateTime.Now.Subtract(item.CreateDate).TotalMinutes;
                    if (ts > 0)
                    {
                        depositOrPaymentRecordResponse.TimeDifference = ts;
                    }
                    depositOrPaymentRecordResponses.Add(depositOrPaymentRecordResponse);
                });
                #endregion

                result.Rows = depositOrPaymentRecordResponses;
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.ErrorMessage = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 重置状态
        /// </summary>
        /// <param name="orderNo">订单号</param>
        /// <returns></returns>
        public bool ResetNoticeStatus(long orderNo)
        {
            var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);
            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"DepositRecord_{month}";
            }

            var depositRecord = this.Get(orderNo);
            if (depositRecord == null)
            {
                throw new Exception("收款记录不存在！");
            }

            var ts = DateTime.Now.Subtract(depositRecord.CreateDate).TotalMinutes;

            if (depositRecord.NoticeStatus == (sbyte)DepositRecordNoticeStatus.未通知){

                if (ts < 6) {
                    throw new Exception("收款记录未通知[重置]条件,时间差大于6分钟");
                }

                depositRecord.NoticeStatus = (sbyte)DepositRecordNoticeStatus.通知失败;
                depositRecord.NoticeTimes = 1;
                depositRecord.NoticeLastTime = DateTime.Now;
                var updateSql = $"update {tableName} set NoticeStatus={depositRecord.NoticeStatus},NoticeTimes='{depositRecord.NoticeTimes}',NoticeLastTime='{depositRecord.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' where  OrderNo={orderNo} and NoticeStatus={(sbyte)DepositRecordNoticeStatus.未通知}";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(updateSql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("数据库操作失败！");
                }
                return true;
            }
            else if (depositRecord.NoticeStatus == (sbyte)DepositRecordNoticeStatus.通知失败){

                if (ts < 6 || depositRecord.NoticeTimes != 5) {
                    throw new Exception("收款记录通知失败[重置]条件,时间差大于6分钟且通知次数等于5");
                }

                depositRecord.NoticeTimes = 1;

                var updateSql = $"update {tableName} set NoticeTimes='{depositRecord.NoticeTimes}' where OrderNo={orderNo} and NoticeStatus={(sbyte)DepositRecordNoticeStatus.通知失败} and NoticeTimes=5";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(updateSql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("数据库操作失败！");
                }
                return true;
            }
            else {
                throw new Exception("收款订单通知状态已发生变化或条件不满足，无法重置");
            }
        }

        /// <summary>
        /// 收款匹配记录(补推送)
        /// </summary>
        /// <param name="orderNo">订单号</param>
        /// <returns></returns>
        public int ReceiptMatchMakeUp(long orderNo)
        {
            var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);
            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"DepositRecord_{month}";
            }

            var depositRecord = this.Get(orderNo);
            if (depositRecord == null)
            {
                throw new Exception("收款记录不存在！");
            }

            var updateSql = $"update {tableName} set NoticeStatus={(sbyte)DepositRecordNoticeStatus.通知失败},NoticeTimes=1, NoticeLastTime='{DateTime.Now:yyyy-MM-dd HH:mm:ss}' where OrderNo={orderNo} and ((NoticeStatus={(sbyte)DepositRecordNoticeStatus.通知失败} and NoticeTimes=5) or (NoticeStatus={(sbyte)DepositRecordNoticeStatus.不通知} and NoticeTimes=0))";

            var updateStatus = _context.Database.ExecuteSqlCommand(updateSql);

            return updateStatus;
        }

        /// <summary>
        /// 获取交易类型的值
        /// </summary>
        /// <param name="configValue"></param>
        /// <returns></returns>
        private string GetTransType(int configValue)
        {
            var q = _context.SysConfig.Where(e => e.CompanyId == (int)ReceiptTrans.TranCompanyId
            && e.ConfigValue == configValue
            && e.ConfigCode == "Transtype").FirstOrDefault();//把Transtype移到Enum下
            if (q != null)
            {
                return q.ConfigContent;
            }
            return string.Empty;
        }

        /// <summary>
        /// 补推送
        /// </summary>
        /// <param name="request"></param>
        public JResult MakeUp(MakeUpRequest request)
        {
            var result = new JResult() {
                Success = false
            };


            if (!request.UserPermission.Contains("ReceiptRecord0003"))
            {
                result.ErrorMessage = "你没有权限操作";
                return result;
            }

            var depositRecord = this.Get(request.OrderNo);
            if (depositRecord.NoticeStatus != (sbyte)DepositRecordNoticeStatus.不通知)
            {
                result.ErrorMessage = "只有通知状态为[不通知]才能补推送";
                return result;
            }

            try
            {
                depositRecord.NoticeStatus = (sbyte)DepositRecordNoticeStatus.通知失败;
                depositRecord.NoticeTimes = 1;
                depositRecord.NoticeLastTime = DateTime.Now;

                var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

                var month = GetOrderMonth(request.OrderNo);

                if (month != DateTime.Now.ToString("MM"))
                {
                    tableName = $"DepositRecord_{month}";
                }
                var updateSql = $"update {tableName} set NoticeStatus={depositRecord.NoticeStatus},NoticeTimes='{depositRecord.NoticeTimes}',NoticeLastTime='{depositRecord.NoticeLastTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' where OrderNo={request.OrderNo} and NoticeStatus={(sbyte)DepositRecordNoticeStatus.不通知}";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(updateSql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("数据库操作失败！");
                }

                result.Success = true;
                result.SuccessMessage = "补推送操作成功";
            }
            catch (Exception ex) {
                result.ErrorMessage = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 改变公司
        /// </summary>
        /// <param name="request"></param>
        public JResult ChangeCompany(ChangeCompanyRequest request)
        {
            var result = new JResult()
            {
                Success = false
            };

            var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(request.OrderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"DepositRecord_{month}";
            }

            /*获取付款记录*/
            var paymentRecord = this.Get(request.OrderNo);
            if (paymentRecord is null)
            {
                throw new Exception("获取收款记录失败");
            }

            if (request.CompanyId == paymentRecord.CompanyId)
            {
                throw new Exception("公司未发生变化");
            }

            var updateStatusPaymentRecordSql = $"update {tableName} set CompanyId={request.CompanyId} WHERE DepositDate IS NOT NULL and NoticeStatus=3 and NoticeTimes=5 and OrderNo={paymentRecord.OrderNo}";

            var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(updateStatusPaymentRecordSql);
            if (updateStatusPaymentRecord <= 0)
            {
                throw new Exception("改变渠道失败、条件不满足！");
            }

            result.Success = true;
            return result;
        }
    }
}
